package com.situ.storehouse.util;

import java.lang.reflect.Field;
//数据库工具类
//创建数据库连接池
//c3p0 DBCP druid
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;


public class Dbutil {
	private static DataSource dataSource;
	static Properties ps = new Properties();
	static {
		try {
			ps.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
			dataSource = DruidDataSourceFactory.createDataSource(ps);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static long executeCount(String sql,Object...parms) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = Dbutil.getConnection();
			ps = conn.prepareStatement(sql);
			if (parms != null) {
				for (int i = 0; i < parms.length; i++) {
					ps.setObject(i + 1, parms[i]);
				}
			}
			rs = ps.executeQuery();
			rs.next();//指向结果集第一行
			return rs.getLong(1);
			
			
		}catch(Exception e) {
			return 0;
		}finally {
			Dbutil.close(rs, ps, conn);
		}
		
	}
	// 封装一个通用方法，用来执行DQL
	public static <T> List<T> executeDQL(String sql, Class<T> c, Object... parms) {
		List<T> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = Dbutil.getConnection();
			ps = conn.prepareStatement(sql);
			if (parms != null) {
				for (int i = 0; i < parms.length; i++) {
					ps.setObject(i + 1, parms[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();// 获取原数据，即表结构
			while (rs.next()) {
				T obj = c.newInstance();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					Object value = rs.getObject(i);// 获取第i列的值
					String label = rsmd.getColumnName(i);// 获取第i列的表头名
					String name=StringDbutil.executeString(label);//表头名规范化
					try {
						Field field = c.getDeclaredField(name);// 找到跟表头名重名的成员变量
						field.setAccessible(true);// 该权限
						field.set(obj, value);
					} catch (Exception e) {
						System.out.println(label + "这一列没找到！");
					}
				}
				list.add(obj);
			}
		} catch (Exception e) {
			System.out.println("查询失败！");
			e.printStackTrace();
		} finally {
			Dbutil.close(rs, ps, conn);
		}
		return list;

	}

	// 封装一个通用方法，用来执行DML
	public static int executeDML(String sql, Object... parms) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			if (parms != null) {
				for (int i = 0; i < parms.length; i++) {
					ps.setObject(i + 1, parms[i]);
				}
			}
			
			int row=ps.executeUpdate();
			return row;
			
		} catch (Exception e) {
			System.out.println("执行DML语句错误！");
		} finally {
			Dbutil.close(ps, conn);
		}
		return 0;
	}

	// 用来获取连接
	public static Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}

	// 用来关闭资源
	public static void close(PreparedStatement ps, Connection conn) {
		close(null, ps, conn);
	}

	public static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	

}
